"""
分类模型
映射数据库categories表
"""
from sqlalchemy import Column, Integer, String, Text, DateTime, ForeignKey, Index
from sqlalchemy.dialects.mysql import TINYINT
from sqlalchemy.orm import relationship

from app.models.base import BaseModel


class Category(BaseModel):
    """
    分类模型类
    对应数据库中的categories表
    """
    
    __tablename__ = "categories"
    
    id = Column(
        Integer, 
        primary_key=True, 
        autoincrement=True,
        comment='分类ID'
    )
    
    name = Column(
        String(50), 
        nullable=False,
        unique=True,
        comment='分类名称'
    )
    
    alias = Column(
        String(50), 
        nullable=False,
        unique=True,
        comment='分类别名（URL友好）'
    )
    
    icon = Column(
        String(50), 
        nullable=True,
        comment='分类图标'
    )
    
    description = Column(
        Text, 
        nullable=True,
        comment='分类描述'
    )
    
    parent_id = Column(
        Integer, 
        ForeignKey('categories.id'), 
        nullable=True,
        default=0,
        comment='父分类ID，0表示顶级分类'
    )
    
    level = Column(
        Integer, 
        nullable=False, 
        default=1,
        comment='分类层级'
    )
    
    sort_order = Column(
        Integer, 
        nullable=False, 
        default=0,
        comment='排序权重'
    )
    
    video_count = Column(
        Integer, 
        nullable=False, 
        default=0,
        comment='包含的视频数量'
    )
    
    status = Column(
        TINYINT, 
        nullable=False, 
        default=1,
        comment='状态：1-启用，0-禁用'
    )
    
    deleted_at = Column(
        DateTime, 
        nullable=True,
        index=True,
        comment='删除时间（软删除）'
    )
    
    # 关系
    videos = relationship('Video', back_populates='category')
    
    # 自关联关系
    parent = relationship('Category', remote_side=[id], backref='children')
    
    # 索引
    __table_args__ = (
        Index('idx_status', 'status'),
        Index('idx_parent_id', 'parent_id'),
        Index('idx_sort_order', 'sort_order'),
    )